package com.apes.fn.scm.commodityInquiry.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;

import org.springframework.stereotype.Component;

import java.util.Map;

@Component
public class ExternalInquiryDao extends ApesDao {

    /***
     * 获取服务方案相关的要货单
     */
    public JSONArray getProductRequire(Map param) {
        String sSQL= "SELECT A.ID , B.PRODUCT_ID\n" +
                "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B\n" +
                " WHERE A.ID = B.PRODUCT_REQUIRE_ID\n" +
                "   AND A.DELETE_DATE IS NULL\n" +
                "   AND B.QTY_REQUIRE - B.QTY_ENDED > 0\n" +
                "   AND A.SOURCE = :SOURCE_BUSINESS_KEY\n" +
                "   AND Lower(A.SOURCE_BUSINESS_TYPE) = Lower(:SOURCE_BUSINESS_TYPE)\n" +
                "   AND B.PRODUCT_ID = :PRODUCT_ID\n";
        return toJSONArray(find(sSQL, param));
    }

    /***
      获取库存
     */
    public JSONArray getStock(Map param) {
        String sSQL= "\n" +
                "SELECT A.ID, A.STATE,\n" +
                "       NVL((SELECT (C.USABLE_QTY + C.TRANSFER_RECEIVE_QTY +\n" +
                "                    C.PURCHASING_WAY_QTY + C.TRANSFER_IN_BILLING_QTY +\n" +
                "                    C.REQUIRE_BILLING_QTY + C.SALES_CONTROL_QTY -\n" +
                "                    C.FROZEN_QTY) STOCK_AMOUNT\n" +
                "              FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E\n" +
                "             WHERE C.LOCATION_ID = D.ID\n" +
                "               AND D.USAGE_ID = '1'\n" +
                "               AND D.DEPOT_ID = E.ID\n" +
                "               AND E.DEPT_ID = :NEW_DEPT_ID\n" +
                "               AND C.PRODUCT_ID = A.ID\n" +
                "               AND D.VALID = 1), 0) STOCK_AMOUNT,\n" +
                "       NVL((SELECT KYS\n" +
                "              FROM VIEW_QYCKYS C\n" +
                "             WHERE C.SPID = A.ID\n" +
                "               AND C.DDID = :OLD_DEPT_ID), 0) SUB_STOCK,\n" +
                "       NVL((SELECT KYS\n" +
                "              FROM VIEW_ZCKYS C\n" +
                "             WHERE C.SPID = A.ID\n" +
                "               AND C.DDID = :OLD_DEPT_ID), 0) GENERAL_STOCK\n" +
                "  FROM MAS_PRODUCT A\n" +
                " WHERE A.ID = :PRODUCT_ID\n";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 获取定损明细原询价单号
     */
    public String getLossQuotationItemInquiry(Map param) {
        String sSQL= "SELECT A.OLD_INQUIRY_NUMBER\n" +
                "  FROM SER_LOSS_QUOTATION_ITEM A\n" +
                " WHERE A.LOSS_QUOTATION_ID = :ID\n" +
                "   AND A.PRODUCT_ID = :PRODUCT_ID\n" +
                "   AND A.OLD_INQUIRY_NUMBER IS NOT NULL\n" +
                "   AND A.OLD_INQUIRY_NUMBER NOT LIKE 'WC12%'\n" +
                "   AND A.OLD_INQUIRY_NUMBER NOT LIKE 'WX12%'";
        JSONArray array = toJSONArray(find(sSQL, param));

        if (array == null || array.size() == 0) return null;
        return array.getJSONObject(0).getString("OLD_INQUIRY_NUMBER");
    }

    /***
     * 获取定损明细原询价单号
     */
    public String getServicePlanItemInquiry(Map param) {
        String sSQL= "SELECT A.OLD_INQUIRY_NUMBER\n" +
                "  FROM SER_SERVICE_PLAN_ITEM A\n" +
                " WHERE A.SERVICE_PLAN_ID = :ID\n" +
                "   AND A.PRODUCT_ID = :PRODUCT_ID\n" +
                "   AND A.OLD_INQUIRY_NUMBER IS NOT NULL\n" +
                "   AND A.OLD_INQUIRY_NUMBER NOT LIKE 'WC12%'\n" +
                "   AND A.OLD_INQUIRY_NUMBER NOT LIKE 'WX12%'";
        JSONArray array = toJSONArray(find(sSQL, param));

        if (array == null || array.size() == 0) return null;
        return array.getJSONObject(0).getString("OLD_INQUIRY_NUMBER");
    }
}
